/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.RekapMahasiswaReguler;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import library.Koneksi;

/**
 *
 * @author user
 */
public class DataMahasiswaReguler {
    
    RekapMahasiswaReguler mhsReguler;
    
    public List<RekapMahasiswaReguler> daftarMhsRegular(){
        
        List<RekapMahasiswaReguler> listMhsregular = new ArrayList<RekapMahasiswaReguler>();
        Koneksi dbConn = new Koneksi();
//        String sql = "SELECT kuota.TAHUN_AJARAN, kuota.KUOTA_MHS, jumlah_calon_mahasiswa.CALON_MHS_IKUT, jumlah_calon_mahasiswa.CALON_MHS_LULUS "
//                + "FROM jumlah_calon_mahasiswa, kuota "
//                + "WHERE kuota.ID_TAHUN_AJARAN = jumlah_calon_mahasiswa.TAHUN_AJARAN GROUP BY kuota.TAHUN_AJARAN";
        String sql = "Select * from rekap_mhs_reguler";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                String tahun = rs.getString("TAHUN_AKADEMIK");
                
                this.mhsReguler = new RekapMahasiswaReguler();
                mhsReguler.setTahunakademik(rs.getString("TAHUN_AKADEMIK"));
                mhsReguler.setDayaTampung(rs.getString("DAYA_TAMPUNG"));
                mhsReguler.setIkutSeleksi(rs.getString("IKUT_SELEKSI"));
                mhsReguler.setLulusSeleksi(rs.getString("LULUS_SELEKSI"));
//                mhsReguler.setBaruRegularBukanTransfer(getBaruRequlerNonTransfer(tahun));
//                mhsReguler.setBaruTransfer(getBaruTransfer(tahun));
//                mhsReguler.setTotalregularBukanTransfer(getTotalRequlerNonTransfer(tahun)-getTotalLulusRequlerNonTransfer(tahun));
//                mhsReguler.setTotalTransfer(getTotalTransfer(tahun)-getTotalLulusTransfer(tahun));
                mhsReguler.setBaruRegularBukanTransfer(rs.getString("BARU_REG_NONTRANS"));
                mhsReguler.setBaruTransfer(rs.getString("BARU_REG_TRANS"));
                mhsReguler.setTotalregularBukanTransfer(rs.getString("TOTAL_REG_NONTRANS"));
                mhsReguler.setTotalTransfer(rs.getString("TOTAL_REG_TRANS"));
                listMhsregular.add(mhsReguler);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return listMhsregular;
        
    }
    
    public boolean tambahMhsReguler(RekapMahasiswaReguler a){
        boolean hasil = false;
        
        String sql = "insert into rekap_mhs_reguler"
                + "(TAHUN_AKADEMIK, DAYA_TAMPUNG, "
                + "IKUT_SELEKSI, LULUS_SELEKSI, "
                + "BARU_REG_NONTRANS, BARU_REG_TRANS, "
                + "TOTAL_REG_NONTRANS, TOTAL_REG_TRANS) value"
                + "("+a.getTahunakademik()+", "+a.getDayaTampung()+","
                + a.getIkutSeleksi()+", "+a.getLulusSeleksi()+","
                + a.getBaruRegularBukanTransfer()+", "+a.getBaruTransfer()+","
                + a.getTotalregularBukanTransfer()+", "+a.getTotalTransfer()+")";
        
        PreparedStatement st = null;
        Connection conn = Koneksi.getKoneksi();
        try {
            st = conn.prepareStatement(sql);
            hasil = st.execute();
        } catch (SQLException ex) {
            Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return hasil;
    }
    
    public String getBaruRequlerNonTransfer(String tahun){
        String baruRegulerNonTransfer = "-";
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT COUNT(ID_USER) AS JUMLAH FROM mahasiswa WHERE TAHUN_ANGKATAN = '"+tahun+"' AND STATUS_TRANSFER = '0' AND STATUS_REGULER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                baruRegulerNonTransfer = rs.getString("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return baruRegulerNonTransfer;
    }
    
    public String getBaruTransfer(String tahun){
        String baruTransfer = "-";
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT COUNT(ID_USER) AS JUMLAH FROM mahasiswa WHERE TAHUN_ANGKATAN = '"+tahun+"' AND STATUS_TRANSFER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                baruTransfer = rs.getString("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return baruTransfer;
    }
    
    /*SELECT COUNT( ID_USER ) FROM mahasiswa WHERE TAHUN_ANGKATAN <=2010*/
    
    public int getTotalRequlerNonTransfer(String tahun){
        int totalNonTransfer = 0;
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT COUNT(ID_USER) AS JUMLAH FROM mahasiswa WHERE TAHUN_ANGKATAN <= '"+tahun+"' AND STATUS_TRANSFER = '0' AND STATUS_REGULER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                totalNonTransfer = rs.getInt("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return totalNonTransfer;
    }
    
    public int getTotalLulusRequlerNonTransfer(String tahun){
        int totalLulus = 0;
        
        Koneksi dbConn = new Koneksi();
        String sql = "select count(a.id_user) AS JUMLAH from mahasiswa as a, lulusan as b where a.id_user=b.id_user and status_reguler='1' and STATUS_TRANSFER = '0' and tahun_angkatan <= '"+tahun+"'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            System.out.println(sql);
            
            rs = st.executeQuery();
            
            while(rs.next()){
                totalLulus = rs.getInt("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return totalLulus;
    }
    
    public int getTotalTransfer(String tahun){
        int totalTransfer = 0;
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT COUNT(ID_USER) AS JUMLAH FROM mahasiswa WHERE TAHUN_ANGKATAN <= '"+tahun+"' AND STATUS_TRANSFER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                totalTransfer = rs.getInt("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return totalTransfer;
    }
    
    public int getTotalLulusTransfer(String tahun){
        int totalTransfer = 0;
        
        Koneksi dbConn = new Koneksi();
        String sql = "SELECT count(a.id_user) AS JUMLAH from mahasiswa as a, lulusan as b where a.id_user=b.id_user and TAHUN_ANGKATAN <= '"+tahun+"' AND STATUS_TRANSFER = '1'";
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                totalTransfer = rs.getInt("JUMLAH");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DataMahasiswaReguler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return totalTransfer;
    }
    
}
